package com.triumphxx.readingreminderbackend.mapper;

import com.triumphxx.readingreminderbackend.entity.Note;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Optional;

/**
 * @author: wangyp
 * @Date: 2025/11/20
 * @Time: 16:53
 * @Desc: 笔记查询
 **/


public interface NoteMapper {

    // 查询所有笔记（按创建时间倒序）
    @Select("SELECT * FROM notes ORDER BY created_at DESC")
    @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "title", column = "title"),
            @Result(property = "yuqueUrl", column = "yuque_url"),
            @Result(property = "content", column = "content"),
            @Result(property = "createdAt", column = "created_at"),
            @Result(property = "folder", column = "folder"),
            @Result(property = "lastReminded", column = "last_reminded"),
            @Result(property = "userId", column = "user_id"),
            @Result(property = "dbCreatedAt", column = "db_created_at"),
            @Result(property = "dbUpdatedAt", column = "db_updated_at")
    })
    List<Note> findAllNotes();


    // 查询所有笔记（按创建时间倒序）
    @Select("SELECT * FROM notes ORDER BY created_at DESC")
    @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "title", column = "title"),
            @Result(property = "yuqueUrl", column = "yuque_url"),
            @Result(property = "content", column = "content"),
            @Result(property = "createdAt", column = "created_at"),
            @Result(property = "folder", column = "folder"),
            @Result(property = "lastReminded", column = "last_reminded"),
            @Result(property = "userId", column = "user_id"),
            @Result(property = "dbCreatedAt", column = "db_created_at"),
            @Result(property = "dbUpdatedAt", column = "db_updated_at"),
            @Result(property = "reviewSchedules", column = "id",
                    many = @Many(select = "com.triumphxx.readingreminderbackend.mapper.ReviewScheduleMapper.findByNoteId"))
    })
    List<Note> findAll();

    // 根据ID查询笔记
    @Select("SELECT * FROM notes WHERE id = #{id}")
    @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "title", column = "title"),
            @Result(property = "yuqueUrl", column = "yuque_url"),
            @Result(property = "content", column = "content"),
            @Result(property = "createdAt", column = "created_at"),
            @Result(property = "folder", column = "folder"),
            @Result(property = "lastReminded", column = "last_reminded"),
            @Result(property = "userId", column = "user_id"),
            @Result(property = "dbCreatedAt", column = "db_created_at"),
            @Result(property = "dbUpdatedAt", column = "db_updated_at"),
            @Result(property = "reviewSchedules", column = "id",
                    many = @Many(select = "com.triumphxx.readingreminderbackend.mapper.ReviewScheduleMapper.findByNoteId"))
    })
    Optional<Note> findById(Long id);

    // 根据语雀URL查询笔记
    @Select("SELECT * FROM notes WHERE yuque_url = #{yuqueUrl}")
    @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "title", column = "title"),
            @Result(property = "yuqueUrl", column = "yuque_url"),
            @Result(property = "content", column = "content"),
            @Result(property = "createdAt", column = "created_at"),
            @Result(property = "folder", column = "folder"),
            @Result(property = "lastReminded", column = "last_reminded"),
            @Result(property = "userId", column = "user_id"),
            @Result(property = "dbCreatedAt", column = "db_created_at"),
            @Result(property = "dbUpdatedAt", column = "db_updated_at")
    })
    Optional<Note> findByYuqueUrl(String yuqueUrl);

    // 插入笔记
    @Insert("INSERT INTO notes (title, yuque_url, content, created_at, folder, user_id, db_created_at, db_updated_at) " +
            "VALUES (#{title}, #{yuqueUrl}, #{content}, #{createdAt}, #{folder}, #{userId}, #{dbCreatedAt}, #{dbUpdatedAt})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(Note note);

    // 更新笔记
    @Update("UPDATE notes SET title = #{title}, yuque_url = #{yuqueUrl}, content = #{content}, " +
            "created_at = #{createdAt}, folder = #{folder}, last_reminded = #{lastReminded}, " +
            "db_updated_at = #{dbUpdatedAt} WHERE id = #{id}")
    int update(Note note);

    // 删除笔记
    @Delete("DELETE FROM notes WHERE id = #{id}")
    int delete(Long id);

    // 查询今天需要复习的笔记
    @Select("SELECT DISTINCT n.* FROM notes n " +
            "INNER JOIN review_schedules rs ON n.id = rs.note_id " +
            "WHERE rs.completed = 0 AND DATE(rs.scheduled_date) = CURDATE()")
    @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "title", column = "title"),
            @Result(property = "yuqueUrl", column = "yuque_url"),
            @Result(property = "content", column = "content"),
            @Result(property = "createdAt", column = "created_at"),
            @Result(property = "folder", column = "folder"),
            @Result(property = "lastReminded", column = "last_reminded"),
            @Result(property = "userId", column = "user_id"),
            @Result(property = "dbCreatedAt", column = "db_created_at"),
            @Result(property = "dbUpdatedAt", column = "db_updated_at"),
            @Result(property = "reviewSchedules", column = "id",
                    many = @Many(select = "com.triumphxx.readingreminderbackend.mapper.ReviewScheduleMapper.findByNoteId"))
    })
    List<Note> findTodayReviews();
}